package com.atguigu3.blob;

import com.atguigu.bean.Customer;
import com.atguigu.util.JDBCUtils;
import com.atguigu1.connection.ConnectionTest;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;

/**
 * @Description 测试使用PreparedStatement操作Blob类型的数据
 */
public class BlobTest {
  // 向数据表customers中插入Blob类型的字段
  @Test
  public void testInsert() throws Exception {
    Connection conn = JDBCUtils.getConnection();
    String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 袁浩	yuan@qq.com	1992-09-08	(BLOB) 87.73 KB
    ps.setObject(1, "袁浩");
    ps.setObject(2, "yuan@qq.com");
    ps.setObject(3, "1992-09-08");
    // 流
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("girl.jpg");
    ps.setBlob(4, is);
    ps.execute();
    JDBCUtils.closeResource(conn, ps);
  }

  // 查询数据表customers中Blob类型的字段
  @Test
  public void testQuery() {
    Connection conn = null;
    PreparedStatement ps = null;
    InputStream is = null;
    FileOutputStream fos = null;
    ResultSet rs = null;
    try {
      conn = JDBCUtils.getConnection();
      String sql = "select id,name,email,birth,photo from customers where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setInt(1, 19); // 设置有图片的customers.id
      rs = ps.executeQuery();
      if (rs.next()) {
        //			方式一：通过顺序
        //			int id = rs.getInt(1);
        //			String name = rs.getString(2);
        //			String email = rs.getString(3);
        //			Date birth = rs.getDate(4);
        // 方式二：指定字段
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        Date birth = rs.getDate("birth");

        Customer cust = new Customer(id, name, email, birth);
        System.out.println(cust);

        // 将Blob类型的字段下载下来，以文件的方式保存在本地
        Blob photo = rs.getBlob("photo");
        is = photo.getBinaryStream();
        fos = new FileOutputStream("111.jpg"); // idea中保存到了与src同级目录下
        byte[] buffer = new byte[1024];
        int len;
        while ((len = is.read(buffer)) != -1) {
          fos.write(buffer, 0, len);
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {

      try {
        if (is != null) is.close();
      } catch (IOException e) {
        e.printStackTrace();
      }

      try {
        if (fos != null) fos.close();
      } catch (IOException e) {
        e.printStackTrace();
      }

      JDBCUtils.closeResource(conn, ps, rs);
    }
  }
}
